package com.buobao.jdbc.demo;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.buobao.jdbc.demo.entity.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

/**
 * commons dbutils使用示例
 */
public class CommonsDbutilsSample {
    public static void main(String[] args) {
        update();
//        Properties properties = new Properties();
//        String propertiesPath = CommonsDbutilsSample.class.getResource("./druid-config.properties").getPath();
//        try {
//            propertiesPath = URLDecoder.decode(propertiesPath, "UTF-8"); //处理路径编码等问题，如中文空格等
//            properties.load(new FileInputStream(propertiesPath)); //加载配置文件
//        } catch (UnsupportedEncodingException e) {
//            throw new RuntimeException(e);
//        } catch (FileNotFoundException e) {
//            throw new RuntimeException(e);
//        } catch (IOException e) {
//            throw new RuntimeException(e);
//        }
//        try {
//            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//            //下面是使用commons-dbutils的过程
//            QueryRunner runner = new QueryRunner(dataSource);
//            List<Employee> list = runner.query("select * from employee limit ?,10",
//                    new BeanListHandler<>(Employee.class),
//                    new Object[]{1}); //object数组中的数量和sql中的？数量一致
//            //这里查询结束后连接会自动关闭，所以后续首尾的工作都有commons dbutils完成了
//            for (Employee e : list) {
//                System.out.println(e.getEno() + "");
//            }
//        } catch (Exception e) {
//            throw new RuntimeException(e);
//        }
    }

    /**
     * commons-dbutils update示例
     */
    public static void update() {
        Properties properties = new Properties();
        String propertiesPath = CommonsDbutilsSample.class.getResource("./druid-config.properties").getPath();
        try {
            propertiesPath = URLDecoder.decode(propertiesPath, "UTF-8"); //处理路径编码等问题，如中文空格等
            properties.load(new FileInputStream(propertiesPath)); //加载配置文件
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        Connection connection = null;
        try {
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            connection = dataSource.getConnection();
            connection.setAutoCommit(false); //开启手动事务
            String sql1 = "update employee set salary=salary+100 where eno=?";
            String sql2 = "update employee set salary=salary-500 where eno=?";

            QueryRunner queryRunner = new QueryRunner();
            queryRunner.update(connection,sql1,new Object[]{1001});
            queryRunner.update(connection,sql2,new Object[]{1002});
            connection.commit();
        } catch (Exception e) {
            try {
                if (connection!=null && connection.isClosed()==false) {
                    connection.rollback();
                }
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtil.closeConnection(null,null,connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
